from openpyxl import load_workbook
def get_deal_amount():
    wb = load_workbook('数据汇总.xlsx')
    ws = wb['成交金额']
    deal_amount = [] # [{},{},{}]
    for row in range(2,ws.max_row+1):
        deal_amount.append({
            'name': ws.cell(row=row,column=3).value,
            'total': ws.cell(row=row,column=35).value,
            'target':ws.cell(row=row,column=40).value,
        })
    deal_amount.sort(key=lambda x: x['total'], reverse=True)  # 根据total倒序排序
    return deal_amount

def get_per_month_deal_amount():
    """获取每个月份的销售总额"""
    wb = load_workbook('../data/Excel大屏数据/2021年6月图书销售任务.xlsx',data_only=True)
    ws = wb['年度历史销售数据']
    data = []
    for cell in ws[11]:
        data.append(cell.value)
    month = ws.max_column - 2
    names = [f'{i}月' for i in range(1,month+1)]
    month_data = {
        'names':names,
        'data':data[2:],
    }
    return month_data


def compare_last_month_deal_amount():
    """获取本月和上个月销量"""
    wb = load_workbook('数据汇总.xlsx')
    ws = wb['成交金额']
    compare_data = {
        'names':[cell.value for cell in ws['C'][1:]],
        'deal_amount':[cell.value for cell in ws['AI'][1:]],
        'last_month':[cell.value for cell in ws['AK'][1:]],
    }
    return compare_data


def get_visitor_arrtibute(min_row,max_row):
    """
    获取访客属性
    :return:{'names':[],'values':[]}
    """
    wb = load_workbook('../data/Excel大屏数据/访客属性.xlsx')
    ws = wb.active
    age_data = {}
    names = []
    values = []
    for row in ws.iter_rows(min_row=min_row,max_row=max_row):
        names.append(row[0].value)
        values.append({
            'name': row[0].value,
            'value': row[1].value,
        })
    age_data['names'] = names
    age_data['value'] = values
    return age_data

def get_views_and_visitor_count(sheet_name):
    wb = load_workbook('数据汇总.xlsx',data_only=True)
    ws = wb[sheet_name]
    names = [column.value for column in ws['C'][1:]]
    data = []
    for row in range(2,ws.max_row+1):
        data.append(
            {
                'name' : ws.cell(row=row,column=3).value,
                'value' : ws.cell(row=row,column=35).value
            }
        )
    result = {
        'names' : names,
        'values' : data
    }
    return result


def get_per_day_deal_amount():
    wb = load_workbook('数据汇总.xlsx')
    ws = wb['成交金额']
    books = {}
    books['names'] = [column.value for column in ws['C'][1:]]
    books['date'] = [f"{i}日"for i in range(1,31)]
    data = []
    colors = ['#00FFFF','#FF00FF','#0080FF','#FF6666','#7F00FF','#CCFFE5','#FFFF33','#0000FF','#FFCCFF']
    for num,row in enumerate(ws.iter_rows(min_row=2)):
        row_value = [cell.value for cell in row]
        data.append({
            'data' : row_value[3:33],
            'itemStyle': {
                'normal': {'barBorderRadius': 5, 'color':colors[num] , 'opacity': 1},
            },
            'name': row_value[2],
            'smooth': "true",
            'type': "line"
        })
    books['values'] = data
    return books

def get_additional_deal_data():
    wb = load_workbook('数据汇总.xlsx',data_only=True)
    ws = wb['加购人数']
    additional_deal_data = {
        'names':[column.value for column in ws['C'][1:]],
        'values':[column.value for column in ws['AI'][1:]],
    }
    return additional_deal_data

def get_map_data():
    wb = load_workbook('../data/Excel大屏数据/地域分布信息.xlsx')
    ws = wb.active
    map_data = []
    for row in ws.iter_rows(min_row=3,min_col=2,max_col=4):
        map_data.append({
            'name':row[0].value,
            'view_count':row[1].value,
            'visitor_count':row[2].value
        })
    return map_data

def get_composite_data(wb):
    composite_data = {
        'deal_amount': sum([cell.value for cell in wb['成交金额']['AI'][1:]]),
        'deal_number': sum([cell.value for cell in wb['成交数']['AI'][1:]]),
        'additional_deal_number': sum([cell.value for cell in wb['加购人数']['AI'][1:]]),
        'visitor_count': sum([cell.value for cell in wb['访客数']['AI'][1:]]),
        'views_count': sum([cell.value for cell in wb['浏览量']['AI'][1:]])
    }
    return composite_data

if __name__ == '__main__':
    result = get_map_data()
    print(result)



